{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy as sp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "lst = []\n",
    "data = []\n",
    "with open('F:\\\\下载\\\\test.txt','r', errors='ignore', encoding='utf-8') as file:\n",
    "    for i, line in enumerate(file):\n",
    "        lst.append(line.strip('\\n'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['﻿订单类别', '订单日期', 'NB5日期', '系统', '款号', '采购组', '采购组2', '批次', '色号', '模式',\n",
       "       '工厂', '订单量', '仓库', '波次', '货期', '离厂/到仓', '上市时间', '采购订单号', '跟单',\n",
       "       '地址（核对上市期）', '备注', '阿米巴', '中类', '性别', '吊牌价', '市值', '季节', '计划员', '件数/箱',\n",
       "       '箱数', '出货周数', '12'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [lst[i:i + 32] for i in range(0,len(lst),32)]\n",
    "df = pd.DataFrame(data[1:],columns=data[0])\n",
    "df = df.drop(5671)\n",
    "df.to_excel('F:\\\\质检表.xls')\n",
    "# df.tail()\n",
    "df.columns\n",
    "# df['货期'].astype('datetime64[ns]')\n",
    "# df['货期'] = pd.to_datetime(df['货期'],format='%m/%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1901800020111/22\n",
       "2     1901800020411/22\n",
       "3     1901800123311/30\n",
       "39     1901801020112/5\n",
       "48     1901801020212/4\n",
       "Name: 货期款号, dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['货期款号'] = df['款号'] + df['货期']\n",
    "Delivery_id = df['货期款号']\n",
    "unique_Delivery_id = Delivery_id.drop_duplicates()\n",
    "unique_Delivery_id[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count            1039\n",
       "unique           1039\n",
       "top       13018091529\n",
       "freq                1\n",
       "Name: 款号, dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_id = df['款号'].drop_duplicates()\n",
    "unique_id.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     1343\n",
       "unique      49\n",
       "top       12/5\n",
       "freq       164\n",
       "Name: 货期日期, dtype: object"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_Delivery_id\n",
    "unique_Delivery_id_time = unique_Delivery_id.str[11:]\n",
    "unique_Delivery_id_time.name = '货期日期'\n",
    "unique_Delivery_id_time.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>货期款号</th>\n",
       "      <th>货期日期</th>\n",
       "      <th>款号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1901800020111/22</td>\n",
       "      <td>11/22</td>\n",
       "      <td>19018000201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1901800020411/22</td>\n",
       "      <td>11/22</td>\n",
       "      <td>19018000204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1901800123311/30</td>\n",
       "      <td>11/30</td>\n",
       "      <td>19018001233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1901801020112/5</td>\n",
       "      <td>12/5</td>\n",
       "      <td>19018010201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1901801020212/4</td>\n",
       "      <td>12/4</td>\n",
       "      <td>19018010202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                货期款号   货期日期           款号\n",
       "0   1901800020111/22  11/22  19018000201\n",
       "2   1901800020411/22  11/22  19018000204\n",
       "3   1901800123311/30  11/30  19018001233\n",
       "39   1901801020112/5   12/5  19018010201\n",
       "48   1901801020212/4   12/4  19018010202"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_table = pd.concat([unique_Delivery_id, unique_Delivery_id_time], axis=1)\n",
    "new_table['款号'] = new_table['货期款号'].str[:11]\n",
    "# new_table.set_index()\n",
    "new_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>货期款号的订单量</th>\n",
       "      <th>货期款号</th>\n",
       "      <th>货期日期</th>\n",
       "      <th>款号</th>\n",
       "      <th>累计计数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1637</td>\n",
       "      <td>1901800020111/22</td>\n",
       "      <td>11/22</td>\n",
       "      <td>19018000201</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800</td>\n",
       "      <td>1901800020411/22</td>\n",
       "      <td>11/22</td>\n",
       "      <td>19018000204</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>800</td>\n",
       "      <td>1901800123311/30</td>\n",
       "      <td>11/30</td>\n",
       "      <td>19018001233</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1299</td>\n",
       "      <td>1901801020112/5</td>\n",
       "      <td>12/5</td>\n",
       "      <td>19018010201</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>800</td>\n",
       "      <td>1901801020212/4</td>\n",
       "      <td>12/4</td>\n",
       "      <td>19018010202</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    货期款号的订单量              货期款号   货期日期           款号  累计计数\n",
       "0       1637  1901800020111/22  11/22  19018000201     1\n",
       "2        800  1901800020411/22  11/22  19018000204     1\n",
       "3        800  1901800123311/30  11/30  19018001233     1\n",
       "39      1299   1901801020112/5   12/5  19018010201     1\n",
       "48       800   1901801020212/4   12/4  19018010202     1"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['订单量'].replace('1,730 ', '1730', inplace=True)\n",
    "df['订单量'] = df['订单量'].astype(np.int64)\n",
    "# a = df.groupby(['货期款号']).sum()\n",
    "# new_table.head()\n",
    "# a['订单量'].reindex(new_table.index)\n",
    "new_table_last = pd.concat([pd.Series(a['订单量'].values, index=new_table.index), new_table], axis=1)\n",
    "# new_table['款号货期的订单量']\n",
    "# pd.Series(a['订单量'].values, index=new_table.index)\n",
    "new_table_last.rename(columns={0:'货期款号的订单量'}, inplace=True)\n",
    "# pd.Series(new_table_last.groupby(['款号'])['货期款号'].count().values).max()\n",
    "new_table_last['计数'] = 1\n",
    "# new_table_last.head()\n",
    "new_table_last['累计计数'] = new_table_last.groupby(['款号'])['计数'].cumsum()\n",
    "new_table_last.head()\n",
    "new_table_last.drop('计数', axis=1, inplace=True)\n",
    "new_table_last.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "a = new_table_last['累计计数'].max() + 1\n",
    "b = list(unique_id.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lst_a = []\n",
    "for x in range(1,a):\n",
    "    lst_b = []\n",
    "    for y in b:\n",
    "        try:\n",
    "            q = new_table_last.loc[(new_table_last['累计计数']== x) & (new_table_last['款号']== y)]['货期日期'].values[0]\n",
    "            w = new_table_last.loc[(new_table_last['累计计数']== x) & (new_table_last['款号']== y)]['货期款号的订单量'].values[0]\n",
    "        except IndexError as e:\n",
    "            q = ''\n",
    "            w = ''\n",
    "        lst_b.append((q,w))\n",
    "    lst_a.append(lst_b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>款号</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19018000201</td>\n",
       "      <td>(11/22, 1637)</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19018000204</td>\n",
       "      <td>(11/22, 800)</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19018001233</td>\n",
       "      <td>(11/30, 800)</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19018010201</td>\n",
       "      <td>(12/5, 1299)</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19018010202</td>\n",
       "      <td>(12/4, 800)</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "      <td>(, )</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            款号              1     2     3     4     5\n",
       "0  19018000201  (11/22, 1637)  (, )  (, )  (, )  (, )\n",
       "1  19018000204   (11/22, 800)  (, )  (, )  (, )  (, )\n",
       "2  19018001233   (11/30, 800)  (, )  (, )  (, )  (, )\n",
       "3  19018010201   (12/5, 1299)  (, )  (, )  (, )  (, )\n",
       "4  19018010202    (12/4, 800)  (, )  (, )  (, )  (, )"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last = pd.DataFrame(lst_a).T\n",
    "# last.index\n",
    "\n",
    "final_table = pd.concat([pd.Series(unique_id.values, index = last.index), last], axis=1)\n",
    "final_table.columns = ['款号','1','2','3','4','5']\n",
    "final_table.to_excel('F:\\\\final_table.xls')\n",
    "final_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "index 0 is out of bounds for axis 0 with size 0",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-247-b08c673c5528>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlst_a\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mg\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mIndexError\u001b[0m: index 0 is out of bounds for axis 0 with size 0"
     ]
    }
   ],
   "source": [
    "g = lst_a[1][0][0]\n",
    "g[0] is False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method astype in module pandas.core.generic:\n",
      "\n",
      "astype(dtype, copy=True, raise_on_error=True, **kwargs) method of pandas.core.series.Series instance\n",
      "    Cast object to input numpy.dtype\n",
      "    Return a copy when copy = True (be really careful with this!)\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    dtype : data type, or dict of column name -> data type\n",
      "        Use a numpy.dtype or Python type to cast entire pandas object to\n",
      "        the same type. Alternatively, use {col: dtype, ...}, where col is a\n",
      "        column label and dtype is a numpy.dtype or Python type to cast one\n",
      "        or more of the DataFrame's columns to column-specific types.\n",
      "    raise_on_error : raise on invalid input\n",
      "    kwargs : keyword arguments to pass on to the constructor\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    casted : type of caller\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(unique_Delivery_id_time.astype)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
